﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Security;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Collections;
using eepg_contratos.Utilities;

namespace eepg_contratos.Models
{
    #region GeneralModels

    public class ListadoModel
    {
        public String id { get; set; }
        public String descripcion { get; set; }
        public String mensaje { get; set; }
        public String numero { get; set; }
    }

    #endregion

    #region GeneralServices

    public class General
    {
        public ArrayList ListarPersonal(String filtro)
        {
            ConexionModel cm = new ConexionModel();
            OracleConnection con = cm.DesarrolloContratos;
            OracleCommand Comm = new OracleCommand();
            ArrayList vListado = new ArrayList();

            Comm.Connection = con;
            Comm.CommandText = "PKG_GENERAL.PROC_OBTENER_EMPLEADO";
            Comm.CommandType = CommandType.StoredProcedure;
            Comm.Parameters.Add("p_texto", filtro);
            Comm.Parameters.Add("p_datos", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            Comm.Parameters.Add("p_coderror ", OracleDbType.Varchar2, 25).Direction = ParameterDirection.Output;
            Comm.Parameters.Add("p_mensaje", OracleDbType.Varchar2, 100).Direction = ParameterDirection.Output;

            OracleDataReader oReader;
            con.Open();
            oReader = Comm.ExecuteReader();

            ListadoModel l;

            while (oReader.Read())
            {
                l = new ListadoModel();
                l.id = oReader["id"].ToString();
                l.descripcion = oReader["descripcion"].ToString();
                vListado.Add(l);
            }

            Comm.Dispose();
            con.Close();
            GC.Collect();

            return vListado;
        }

        public ArrayList ListarSolicitudes(String filtro)
        {
            ConexionModel cm = new ConexionModel();
            OracleConnection con = cm.DesarrolloContratos;
            OracleCommand Comm = new OracleCommand();
            ArrayList vListado = new ArrayList();

            Comm.Connection = con;
            Comm.CommandText = "PKG_GENERAL.PROC_OBTENER_EMPLEADO";
            Comm.CommandType = CommandType.StoredProcedure;
            Comm.Parameters.Add("p_texto", filtro);
            Comm.Parameters.Add("p_datos", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            Comm.Parameters.Add("p_coderror ", OracleDbType.Varchar2, 25).Direction = ParameterDirection.Output;
            Comm.Parameters.Add("p_mensaje", OracleDbType.Varchar2, 100).Direction = ParameterDirection.Output;

            OracleDataReader oReader;
            con.Open();
            oReader = Comm.ExecuteReader();

            ListadoModel l;

            while (oReader.Read())
            {
                l = new ListadoModel();
                l.id = oReader["id"].ToString();
                l.descripcion = oReader["descripcion"].ToString();
                vListado.Add(l);
            }

            Comm.Dispose();
            con.Close();
            GC.Collect();

            return vListado;
        }

        public String ListarPersonalDt(String filtro)
        {
            ConexionModel cm = new ConexionModel();
            OracleConnection con = cm.DesarrolloContratos;
            OracleCommand Comm = new OracleCommand();
            
            Comm.Connection = con;
            Comm.CommandText = "PKG_GENERAL.PROC_OBTENER_EMPLEADO";
            Comm.CommandType = CommandType.StoredProcedure;
            Comm.Parameters.Add("p_texto", filtro);
            Comm.Parameters.Add("p_datos", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            Comm.Parameters.Add("p_coderror ", OracleDbType.Varchar2, 25).Direction = ParameterDirection.Output;
            Comm.Parameters.Add("p_mensaje", OracleDbType.Varchar2, 100).Direction = ParameterDirection.Output;

            OracleDataReader oReader;
            con.Open();
            oReader = Comm.ExecuteReader();

            ListadoModel l;

            DataTable dt = new DataTable("personal");
            dt.Columns.Add("id");
            dt.Columns.Add("descripcion");

            while (oReader.Read())
            {
                l = new ListadoModel();
                l.id = oReader["id"].ToString();
                l.descripcion = oReader["descripcion"].ToString();
                
                DataRow dr = dt.Rows.Add();
                dr["id"] = l.id;
                dr["descripcion"] = l.descripcion;

            }

            Comm.Dispose();
            con.Close();
            GC.Collect();

            AppUtil util = new AppUtil();
            
            return util.ConvertDatatableToXML(dt);
        }

    }
    #endregion
}